#!/user/bin/evn Python3 
# -*- coding:utf-8 -*-
# project:PythonForWork
# Management.py
# 工具管理类，可实现上下文管理或者常用工具
# author:ZhaoHeXin
# creation time:2019/11/29--14:02


import sqlite3

import cx_Oracle
import jpype
import os
import openpyxl


class SqliteManagement(object):
    """
    sqlite 数据库管理，实现with调用结束后自动关闭游标及数据库对象
    """

    def __init__(self, path):
        """
        接收数据库参数
        :param path: sqlite数据库绝对地址
        :return: 返回数据库游标，可以直接进行数据库操作
        """
        self.path = path

    def __enter__(self):
        # 连接数据库
        self.conn = sqlite3.connect(self.path)
        # 创建游标并返回游标
        self.cursor = self.conn.cursor()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        # 更新数据库
        self.conn.commit()
        # 关闭游标及数据库链接
        self.cursor.close()
        self.conn.close()


class OracleManagement(object):
    """
    oracle 数据库管理，实现with调用结束后自动关闭游标及数据库对象
    """

    def __init__(self, name):
        """
        接收oracle相关参数
        :param name:数据库用户名 格式为"*/*@*.*.*.*:*/*"
        :return: 返回数据库游标，可直接进行数据库操作及数据库连接
        """
        self.name = name

    def __enter__(self):
        # 连接数据库并创建返回游标
        self.conn = cx_Oracle.connect(self.name)
        self.cursor = self.conn.cursor()
        return self.conn, self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        # 更新数据库
        self.conn.commit()
        # 关闭游标及数据库
        self.cursor.close()
        self.conn.close()


class OpenJavaJar(object):
    """
    JavaJar工具管理
    """

    def __init__(self, path):
        """
        打开java jar包with方法
        :param path:jar包路径
        """
        self.path = os.path.join(os.path.abspath('.'), path)

    def __enter__(self):
        # 启动jar
        jpype.startJVM(jpype.getDefaultJVMPath(), "-ea", f"-Djava.class.path={self.path}")
        return True

    def __exit__(self, exc_type, exc_val, exc_tb):
        # 关闭jar
        jpype.shutdownJVM()


class OpenExcel(object):
    """
    Excel文件管理，实现自动打开/关闭Excel方法
    """

    def __init__(self, path):
        """
        打开Excel with方法
        @param path:Excel文件路径
        """
        self.path = path

    def __enter__(self):
        # 判断文件是否存在，打开Excel 并返回Excel对象
        if os.path.exists(self.path):
            self.excel = openpyxl.load_workbook(self.path)
        else:
            self.excel = openpyxl.Workbook()
        return self.excel

    def __exit__(self, exc_type, exc_val, exc_tb):
        # 关闭并存储Excel,默认删除Sheet
        del sexcel["Sheet"]
        self.excel.save(self.path)

    def __str__(self):
        return "该函数方法为打开Excel的with方法"


if __name__ == '__main__':
    # # 调试sqlite
    # with SqliteManagement(
    #         *****) as f:
    #     result = f.execute("select * from ")
    #     for i in result:
    #         print(i)

    # 调试oracle
    # with OracleManagement("adkgj", "adkglpassword@192.168.1.219:1521/tjadk") as f:
    #     result = f.execute("select * from GH_BB_BBZC")
    #     for i in result:
    #         print(i)
    # conn = cx_Oracle.connect("adkgj", "adkglpassword@192.168.1.219:1521/tjadk")
    # conn = cx_Oracle.connect("cjcl/cjclpassword@192.168.1.219:1521/ytb_gj")
    # # 调试打开jar包
    # path = r"E:\Python\Python\BackupPackageRepair\file\myjar.jar"
    # with OpenJavaJar(path) as f:
    #     Class = jpype.JClass('com.thtf.zhx.DesCryptUtil')  # 调用类
    #     DesCryptUtil = Class()  # 实例化
    with OpenExcel(r"C:\Users\ZhaoHeXin\Desktop\测试.xlsx") as excel:
        # 创建sheet
        sheet = excel.create_sheet("周日")
        print(excel.sheetnames)
    pass
